﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using System.Data.OleDb;

namespace RPMiniMarketServer.DataBase
{
    class SuperMarketDB
    {


        public OleDbConnection connect()
        {
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\solver\servidornet\SuperMercado\SUPERMERCADO.MDB; Jet OLEDB:Database Password=%SolKerMediaP;");

            try
            {

                conn.Open();

                conn.Close();

                return conn;
            }
            catch(Exception ex)
            {
                conn.Close();


                Console.WriteLine(ex.ToString());
                return null;

            }

        }

        public double getProductPrice(long barCode)
        {

            OleDbConnection conn = connect();

            if (conn == null)
            {
                throw new Exception("Connection lost.");
            }

            try
            {
                conn.Open();

                OleDbCommand accessCommand = new OleDbCommand("SELECT Precio FROM Productos PA INNER JOIN Productos_CodigosExtra PC ON PA.Codigo = PC.Codigo WHERE PC.Codigo_Extra = @barCode;", conn);
                accessCommand.Parameters.Add("@barCode",OleDbType.VarChar).Value = barCode.ToString();

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(accessCommand);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet, "Productos");

                dataAdapter.Dispose();
                conn.Close();

                if (dataSet.Tables["Productos"].Rows.Count > 0)
                {
                    Console.WriteLine("--------------------------------------------------");
                    Console.WriteLine(dataSet.Tables["Productos"].Rows[0][0].ToString());

                    double result = (double)dataSet.Tables["Productos"].Rows[0][0];

                    return result;
                }
                else
                {
                    throw new Exception("Product does not exists.");
                }
            }
            catch (System.Exception ex)
            {
                conn.Close();

                throw ex;
            }

        }

        public List<ProductPrice> getProductsPrice(List<long> barCodes)
        {

            OleDbConnection conn = connect();

            if (conn == null)
            {
                throw new Exception("Connection lost.");
            }

            try
            {
                conn.Open();

                OleDbCommand accessCommand = new OleDbCommand();

                accessCommand.Connection = conn;

                String query = "SELECT PC.Codigo_Extra, Precio FROM Productos PA INNER JOIN Productos_CodigosExtra PC ON PA.Codigo = PC.Codigo WHERE PC.Codigo_Extra IN (";

                for (int x = 0; x < barCodes.Count; x++)
                {
                    query += " @" + x.ToString() + ",";

                    accessCommand.Parameters.Add("@"+x, OleDbType.VarChar).Value = barCodes[x].ToString();
                }

                query += ");";

                accessCommand.CommandText = query;

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(accessCommand);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet, "Productos");

                dataAdapter.Dispose();
                conn.Close();

                if (dataSet.Tables["Productos"].Rows.Count > 0)
                {
                    List<ProductPrice> results = new List<ProductPrice>();

                    ProductPrice product;

                    foreach (DataRow row in dataSet.Tables["Productos"].Rows)
                    {
                        product = new ProductPrice();

                        product.BarCode = long.Parse((string)row[0]);
                        product.Price = (double)row[1];
                        

                        results.Add(product);
                    }
                    

                    return results;
                }
                else
                {
                    throw new Exception("Products not found.");
                }
            }
            catch (System.Exception ex)
            {
                conn.Close();

                throw ex;
            }

        }

        public int getProductStock(long barCode)
        {

            OleDbConnection conn = connect();

            if (conn == null)
            {
                throw new Exception("Connection lost.");
            }

            try
            {
                conn.Open();

                OleDbCommand accessCommand = new OleDbCommand("SELECT STOCK FROM Productos PA INNER JOIN Productos_CodigosExtra PC ON PA.Codigo = PC.Codigo WHERE PC.Codigo_Extra = @barCode;", conn);
                accessCommand.Parameters.Add("@barCode", OleDbType.VarChar).Value = barCode.ToString();

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(accessCommand);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet, "Productos");

                dataAdapter.Dispose();
                conn.Close();

                if (dataSet.Tables["Productos"].Rows.Count > 0)
                {

                    double result = (double)dataSet.Tables["Productos"].Rows[0][0];

                    return (int) result;
                }
                else
                {
                    throw new Exception("Product does not exists.");
                }
            }
            catch (System.Exception ex)
            {
                conn.Close();

                throw ex;
            }

        }

        public List<int> getProductsStock(List<long> barCodes)
        {

            OleDbConnection conn = connect();

            if (conn == null)
            {
                throw new Exception("Connection lost.");
            }

            try
            {
                conn.Open();

                OleDbCommand accessCommand = new OleDbCommand();

                accessCommand.Connection = conn;

                String query = "SELECT STOCK FROM Productos PA INNER JOIN Productos_CodigosExtra PC ON PA.Codigo = PC.Codigo WHERE PC.Codigo_Extra IN (";

                for (int x = 0; x < barCodes.Count; x++)
                {
                    query += " @" + x.ToString() + ",";

                    accessCommand.Parameters.Add("@" + x, OleDbType.VarChar).Value = barCodes[x].ToString();
                }

                query += ");";

                accessCommand.CommandText = query;

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(accessCommand);

                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet, "Productos");

                dataAdapter.Dispose();
                conn.Close();

                if (dataSet.Tables["Productos"].Rows.Count > 0)
                {
                    List<int> results = new List<int>();

                    foreach (DataRow row in dataSet.Tables["Productos"].Rows)
                    {
                        double result = (double)row[0];

                        results.Add((int)result);
                    }


                    return results;
                }
                else
                {
                    throw new Exception("Product does not exists.");
                }
            }
            catch (System.Exception ex)
            {
                conn.Close();

                throw ex;
            }

        }

    }
}
